package com.dao;

import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import com.common.DBConn;
import com.entity.GoodsInfo;
import com.entity.PageModel;
import com.util.PublicToolCheckParam;

public class GoodsInfoDAO {

	public void addGoodsInfo(String typeDetailInfo, String goodsName,
			double price, float discount, int isNew, int isRecommend,
			int status, String photo, String remark, int countRequire,
			String thirdId, String iscoold) {

		String sql = null;
		if (!PublicToolCheckParam.checkNullAndEmpty(remark)) {
			remark = "无";
		}
		// if(!PublicToolCheckParam.checkNullAndEmpty(thirdId)){
		// thirdId = null;
		// }

		SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

		String createdate = sdf.format(new Date());

		if (PublicToolCheckParam.checkNullAndEmpty(photo)) {
			if (PublicToolCheckParam.checkNullAndEmpty(thirdId)) {

				sql = "insert into GoodsInfo (`typeDetailInfo`, `goodsName`, `price`, `discount`, `isNew`, `isRecommend`, `status`, `photo`, `remark`, `countRequire`, `createdate`, `userid`, `isCooled`) values('"
						+ typeDetailInfo
						+ "','"
						+ goodsName
						+ "',"
						+ price
						+ ","
						+ discount
						+ ","
						+ isNew
						+ ","
						+ isRecommend
						+ ","
						+ status
						+ ",'"
						+ photo
						+ "','"
						+ remark
						+ "',"
						+ countRequire
						+ ",'"
						+ createdate
						+ "','"
						+ thirdId
						+ "','" + iscoold + "')";
			} else {
				sql = "insert into GoodsInfo (`typeDetailInfo`, `goodsName`, `price`, `discount`, `isNew`, `isRecommend`, `status`, `photo`, `remark`, `countRequire`, `createdate`, `userid`, `isCooled`) values('"
						+ typeDetailInfo
						+ "','"
						+ goodsName
						+ "',"
						+ price
						+ ","
						+ discount
						+ ","
						+ isNew
						+ ","
						+ isRecommend
						+ ","
						+ status
						+ ",'"
						+ photo
						+ "','"
						+ remark
						+ "',"
						+ countRequire
						+ ",'"
						+ createdate
						+ "',"
						+ null
						+ ",'"
						+ iscoold + "')";
			}

		} else {
			if (PublicToolCheckParam.checkNullAndEmpty(thirdId)) {

				sql = "insert into GoodsInfo (`typeDetailInfo`, `goodsName`, `price`, `discount`, `isNew`, `isRecommend`, `status`, `photo`, `remark`, `countRequire`, `createdate`, `userid`, `isCooled`) values('"
						+ typeDetailInfo
						+ "','"
						+ goodsName
						+ "',"
						+ price
						+ ","
						+ discount
						+ ","
						+ isNew
						+ ","
						+ isRecommend
						+ ","
						+ status
						+ ","
						+ null
						+ ",'"
						+ remark
						+ "',"
						+ countRequire
						+ ",'"
						+ createdate
						+ "','"
						+ thirdId
						+ "','" + iscoold + "')";
			} else {

				sql = "insert into GoodsInfo (`typeDetailInfo`, `goodsName`, `price`, `discount`, `isNew`, `isRecommend`, `status`, `photo`, `remark`, `countRequire`, `createdate`, `userid`, `isCooled`)values('"
						+ typeDetailInfo
						+ "','"
						+ goodsName
						+ "',"
						+ price
						+ ","
						+ discount
						+ ","
						+ isNew
						+ ","
						+ isRecommend
						+ ","
						+ status
						+ ","
						+ null
						+ ",'"
						+ remark
						+ "',"
						+ countRequire
						+ ",'"
						+ createdate
						+ "',"
						+ null
						+ ",'"
						+ iscoold + "')";

			}
		}

		DBConn.ExecuteUD(sql);
	}

	public void deleteGoodsInfo(int goodsId) {

		String sql = "delete from GoodsInfo where goodsId=" + goodsId;
		DBConn.ExecuteUD(sql);

	}

	public ArrayList<Object> getNewGoodsInfo() {

		String sql = "select * from GoodsInfo where   iscooled = 0 order by goodsId desc limit 0,8";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	public ArrayList<Object> getPageModel(PageModel pm, String typeDetailInfo) {

		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		} else if (pm.getSumCount() < pm.getEveryPageCount()) {

			everyPageProductCount = pm.getSumCount();

		}
		String sql = "select * from (select  * from (select  * from GoodsInfo where typeDetailInfo = '"
				+ typeDetailInfo
				+ "' order by goodsId limit 0,"
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a order by goodsId desc limit 0,"
				+ everyPageProductCount + ") as b order by goodsId";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());

	}

	/**
	 * 查询所有坐诊信息
	 * @param pm
	 * @return
	 */
	public ArrayList<Object> getPageModelOfAllGoods(PageModel pm) {

		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		} else if (pm.getSumCount() < pm.getEveryPageCount()) {

			everyPageProductCount = pm.getSumCount();

		}
		String tsql = "SELECT * from GoodsInfo a "
					+ " ORDER BY a.createdate DESC"
					+ " LIMIT " + (currentPage - 1) * pm.getPageSize() +","+currentPage * pm.getPageSize();
		return DBConn.ExecuteQuery(tsql, new GoodsInfo());

	}

	// show third goods
	public ArrayList<Object> getPageModelOfAllGoodsforthirdId(PageModel pm,
			String thirdId) {

		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		} else if (pm.getSumCount() < pm.getEveryPageCount()) {

			everyPageProductCount = pm.getSumCount();

		}

		String tsql = "SELECT * from GoodsInfo a where a.userid = " + thirdId
					+ " ORDER BY a.createdate DESC"
					+ " LIMIT " + (currentPage - 1) * pm.getPageSize() +","+currentPage * pm.getPageSize();
		
		return DBConn.ExecuteQuery(tsql, new GoodsInfo());

	}

	/**
	 * 查询医生信息表
	 * @param gid
	 * @return
	 */
	public GoodsInfo getQueryGoodsInfo(int gid) {

		String sql = "select * from GoodsInfo where goodsId=" + gid;

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new GoodsInfo());
		if(list != null && list.size() > 0){
			return (GoodsInfo) list.get(0);
		}else{
			return null;
		}
	}

	public ArrayList<Object> getRecommendGoodsInfo() {

		String sql = "select * from GoodsInfo where photo is not null and status = 0 and iscooled=0 order by goodsId desc LIMIT 0,9 ";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	public int getSearchSumGoodsCount(String key) {

		String sql = "select * from GoodsInfo where goodsName like '%" + key
				+ "%'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new GoodsInfo());
		if (list != null) {
			return list.size();
		}

		return 0;
	}

	public int gettypelistGoodsCount(String typeid) {

		String tsql = "SELECT" + 
				"	a.*" + 
				"FROM" + 
				"	GoodsInfo a" + 
				" JOIN goodstypedetailinfo b ON a.typeDetailInfo = b.Id" + 
				" WHERE b.typeId=" + typeid +" AND a.isCooled = 0 ";
		
		/*String sql = "select * from GoodsInfo where  status = 0  and iscooled=0 and typeDetailInfo ='" + typeid
				+ "'";*/
		ArrayList<Object> list = DBConn.ExecuteQuery(tsql, new GoodsInfo());
		if (list != null) {
			return list.size();
		}

		return 0;
	}

	public int getSearchSumGoodsCountforthirdId(String key, String thirdId) {

		String sql = "select * from GoodsInfo where goodsName like '%" + key
				+ "%' and userid='" + thirdId + "'";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new GoodsInfo());
		if (list != null) {
			return list.size();
		}

		return 0;
	}

	public ArrayList<Object> getSpecialGoodsInfo() {

		String sql = "select * from GoodsInfo where discount>0 and  status = 0  and iscooled=0 order by discount LIMIT 0,9 ";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	public int getSumGoodsCount() {

		String sql = "select * from GoodsInfo";
		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new GoodsInfo());
		if (list != null) {
			return list.size();
		}

		return 0;
	}

	// get third goods numb
	public int getSumGoodsCountforthirdId(String thirdId) {
		String tsql = "SELECT * from GoodsInfo a where a.userid = " + thirdId;
		//String sql = "select * from GoodsInfo where userid='" + thirdId + "'";
		ArrayList<Object> list = DBConn.ExecuteQuery(tsql, new GoodsInfo());
		if (list != null) {
			return list.size();
		}
		return 0;
	}

	public int getSumProductCount(String typeDetailInfo) {

		String sql = "select * from GoodsInfo where typeDetailInfo = '"
				+ typeDetailInfo + "'";

		if (DBConn.ExecuteQuery(sql, new GoodsInfo()) != null) {
			return (DBConn.ExecuteQuery(sql, new GoodsInfo())).size();
		}
		return 0;

	}

	public ArrayList<Object> getTypeGoodsInfo(String typeDetailInfo) {

		String sql = "select * from GoodsInfo where typeDetailInfo = '"
				+ typeDetailInfo + "'";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	public boolean isGoodsExistInGooodsType(int typeId) {

		String sql = "select * from GoodsInfo where typeId=" + typeId;

		if (DBConn.ExecuteQuery(sql, new GoodsInfo()) != null) {
			return true;
		}
		return false;

	}

	public ArrayList<Object> searchProduct(String key) {

		String sql = "select * from GoodsInfo where goodsName like '%" + key
				+ "%'";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	// 前台搜索
	public ArrayList<Object> searchProductPageModelweb(PageModel pm,
			String key, String order) {
		String strorder = "";
		strorder = " order by goodsId desc";
		if (order.equals("price")) {
			strorder = " order by price desc";
		} else if (order.equals("createdate")) {
			strorder = " order by createdate desc";
		}
		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		}
		String sql = "select * from (select  * from (select  * from GoodsInfo where goodsName like '%"
				+ key
				+ "%' "
				+ strorder
				+ " limit 0, "
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a "
				+ strorder
				+ " limit 0,"
				+ everyPageProductCount
				+ ") as b "
				+ strorder + "";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	/**
	 *  前台分类列表坐诊
	 * @param pm
	 * @param typeid
	 * @param order
	 * @return
	 */
	public ArrayList<Object> typelistProductPageModelweb(PageModel pm,
			String typeid, String order) {
		String strorder = "";
		strorder = " order by goodsId desc";
		if (order.equals("price")) {
			strorder = " order by price desc";
		} else if (order.equals("createdate")) {
			strorder = " order by createdate desc";
		}
		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		}
		String tsql = "SELECT" + 
				"	a.*" + 
				" FROM" + 
				"	GoodsInfo a" + 
				" JOIN goodstypedetailinfo b ON a.typeDetailInfo = b.Id" + 
				" WHERE b.typeId=" + typeid +" AND a.isCooled = 0 "
				+strorder
				+ " LIMIT " + (currentPage - 1) * pm.getPageSize() +","+currentPage * pm.getPageSize();
		return DBConn.ExecuteQuery(tsql, new GoodsInfo());
	}

	// show admin all goods for search
	public ArrayList<Object> searchProductPageModel(PageModel pm, String key) {

		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		}
		String sql = "select * from (select   * from (select  * from GoodsInfo where goodsName like '%"
				+ key
				+ "%' order by goodsId limit 0, "
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a order by goodsId desc limit 0,"
				+ everyPageProductCount + ") as b order by goodsId";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	// show third all goods for search
	public ArrayList<Object> searchProductPageModelforthirdId(PageModel pm,
			String key, String thirdId) {

		int everyPageProductCount = pm.getEveryPageCount();
		int currentPage = pm.getCurrentPage();

		if (pm.getSumCount() % everyPageProductCount != 0
				&& currentPage == pm.getLastPage()) {

			everyPageProductCount = pm.getSumCount() % everyPageProductCount;

		}
		String sql = "select * from (select   * from (select  * from GoodsInfo where goodsName like '%"
				+ key
				+ "%' and userid="
				+ thirdId
				+ " order by goodsId limit 0, "
				+ pm.getEveryPageCount()
				* pm.getCurrentPage()
				+ ") as a order by goodsId desc limit 0,"
				+ everyPageProductCount + ") as b order by goodsId";

		return DBConn.ExecuteQuery(sql, new GoodsInfo());
	}

	public void updateGoodsInfo(int goodsId, String typeDetailInfo,
			String goodsName, double price, float discount, int isNew,
			int isRecommend, int status, String photo, String remark,
			int countRequire, String iscool) {

		String sql = null;
		if (!PublicToolCheckParam.checkNullAndEmpty(remark)) {
			remark = "无";
		}
		if (PublicToolCheckParam.checkNullAndEmpty(photo)) {
			sql = "update GoodsInfo set typeDetailInfo='" + typeDetailInfo
					+ "',goodsName='" + goodsName + "',price=" + price
					+ ",discount=" + discount + ",isNew=" + isNew
					+ ",isRecommend=" + isRecommend + ",status=" + status
					+ ",photo='" + photo + "',remark='" + remark
					+ "',countRequire=" + countRequire + ",iscooled=" + iscool
					+ " where goodsId=" + goodsId;

		} else {
			sql = "update GoodsInfo set typeDetailInfo='" + typeDetailInfo
					+ "',goodsName='" + goodsName + "',price=" + price
					+ ",discount=" + discount + ",isNew=" + isNew
					+ ",isRecommend=" + isRecommend + ",status=" + status
					+ ",photo=" + null + ",remark='" + remark
					+ "',countRequire=" + countRequire + ",iscooled=" + iscool
					+ " where goodsId=" + goodsId;
		}

		DBConn.ExecuteUD(sql);
	}

	public void coolGoods(int goodsId, int isCooled) {

		if (isCooled == 1) {
			isCooled = 0;
		} else {
			isCooled = 1;
		}

		String sql = "update GoodsInfo set isCooled = " + isCooled
				+ " where goodsid=" + goodsId;

		DBConn.ExecuteUD(sql);
	}

	public boolean isCooled(int goodsId) {

		String sql = "select * from GoodsInfo where goodsid=" + goodsId;

		ArrayList<Object> list = DBConn.ExecuteQuery(sql, new GoodsInfo());
		if (list != null) {
			if (list.size() == 1) {
				int isCooled = ((GoodsInfo) list.get(0)).getIsCooled();
				if (isCooled == 1) {
					return true;
				}
			}
		}
		return false;
	}

}
